const xlsx = require('xlsx');
const node_xlsx = require('node-xlsx');
const fs = require('fs');
const _ = require('lodash');
var iconv = require('iconv-lite');
const rp = require('request-promise');
const req = require('request');
const moment = require('moment');

let filePath = '/Users/dongchen/Desktop/速递易/北京数大科技有限公司IP对照表-20181123.xlsx';

// let result = xlsx.parse(filePath);

let rs = fs.createReadStream(filePath);

process_RS(rs, async wb => {
    let first_sheet_name = wb.SheetNames[0];
    let ws = wb.Sheets[first_sheet_name];

    let result = {};
    let typeMap = {};
    let xlsData = [];

    let count = 0;
    for (let m in ws) {
        /* all keys that do not begin with "!" correspond to cell addresses */
        if (m[0] === '!') continue;
        let tt = ws[m];
        let value = ws[m].w;
        // let values = value.split(/;+/);
        //
        let type = m.match(/([A-Z])(.*)/);
        if(!type) continue;

        if(type[2] == 1) {
            continue;
        }
        if(type[1] !== 'F'){
            continue;
        }

        let name = 'sdyapi';
        let restKey = 'swhHUSD%&sd12sQ=';
        let iccid = value;
        let auth = "Basic " + new Buffer(`${name}:${restKey}`).toString("base64");

        let result = [];
        let tmp;
        let pageNumber = 0;

        tmp = await rp({
            method: 'GET',
            url: `http://iot.hexingtx.com/cliapi`,
            qs : {
                action : 'cardInfo',
                iccid : iccid
            },
            headers : {"Authorization" : auth},
            json: true
        });

        xlsData.push({
            iccid: value,
            active_time: new moment(tmp.active_time).format('YYYY-MM-DD HH:mm:ss'),
            send_time : ws['E'+type[2]].w,
            status_name: tmp.status_name
        });
        count++;
        // if(count > 3){
        //     break;
        // }
    }

    let buffer = buildExcelResultBuf([{
        sheet : '状态统计',
        title : [{
            key : 'iccid',
            name : 'iccid'
        },{
            key : 'send_time',
            name : '发货时间'
        },{
            key : 'status_name',
            name : '状态'
        },{
            key : 'active_time',
            name : '激活时间'
        }],
        data : xlsData
    }]);
    fs.writeFileSync('output.xlsx', buffer);
    console.log('文档以输出，请查阅。');


    // for(let type in typeMap){
    //     let tmp = result[type];
    //     let re = _.map(tmp, (v, k) => v);
    //     let orderedR = _.orderBy(re, ['count'],['desc']);
    //
    //     xlsData.push({
    //         sheet : typeMap[type],
    //         title : [{
    //             key : 'keyword',
    //             name : typeMap[type]
    //         },{
    //             key : 'count',
    //             name : '频次'
    //         }],
    //         data : orderedR
    //     });
    // }
    //
    //
});

function process_RS(stream/*:ReadStream*/, cb/*:(wb:Workbook)=>void*/)/*:void*/{
    var buffers = [];
    stream.on('data', function(data) { buffers.push(data); });
    stream.on('end', function() {
        var buffer = Buffer.concat(buffers);
        // buffer = iconv.decode(buffer,'gbk');
        var workbook = xlsx.read(buffer, {type:"buffer"});

        /* DO SOMETHING WITH workbook IN THE CALLBACK */
        cb(workbook);
    });
}

function xlsxParser(file, handler) {
    var result = {code: 1, msg: ''};

    if (!file.originalname.endsWith('xlsx') && !file.originalname.endsWith('xls')) {
        result.code = 0;
        result.msg = '上传文件格式不正确，请上传excel文件';
        return result;
    }
    var arr = [];
    for (var i = 0; i != file.buffer.length; ++i) {
        arr[i] = String.fromCharCode(file.buffer[i]);
    }
    var bstr = arr.join('');

    var wb = XLSX.read(bstr, {type: 'binary'});

    var first_sheet_name = wb.SheetNames[0];

    /* Get worksheet */
    var ws = wb.Sheets[first_sheet_name];
    return handler(ws);
}

/**
 * 构建xlsx内容，opts中每一项为{sheet：String, title: [], data: []}
 * @param opts = Array
 * @returns Buffer
 */
function buildExcelResultBuf(opts){
    let finData = [];
    for (let sheet of opts) {
        let data = [];
        let title = sheet.title.map(function (item) {
            return item.name;
        });
        data.push(title);

        for (let key in sheet.data) {
            let row = [];
            _.each(sheet.title, function(item){
                row.push(sheet.data[key][item.key]);
            });
            data.push(row);
        }
        finData.push({name : sheet.sheet, data : data});
    }

    let buffer = node_xlsx.build(finData);
    return buffer;
}